#coding=utf-8
import MySQLdb
import uuid
from  datetime  import  *
from collections import namedtuple



conn= MySQLdb.connect(
        host='192.168.145.44',
        port = 5506,
        user='root',
        passwd='mysql',
        db ='contract_product-2017-9-9',
        charset="utf8"
        )
cur = conn.cursor()

#创建数据表
#cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))")

#插入一条数据
#cur.execute("insert into student values('2','Tom','3 year 2 class','9')")


reCount = cur.execute("select * from yx_finance_payment")
# result = cur.fetchall()


#修改查询条件的数据
# cur.execute("update base_car_brand set logo='3 year 1 class' where ID = '2c95802b55b592240155b5aebb3b0eb1'")

#删除查询条件的数据
#cur.execute("delete from student where age='9'")

def generate_namedtuple(cur):
    from collections import namedtuple
    fieldnames = [d[0].lower() for d in cur.description]
    Record = namedtuple('Record', fieldnames)
    rows = cur.fetchall()
    if not rows:return
    else:
        return map(Record._make, rows)

for k in generate_namedtuple(cur):
        origin_id = k.id
        trace_id = uuid.uuid1()
        trace_id = str(trace_id).replace("-","")

        apply_id = uuid.uuid1()
        apply_id = str(apply_id).replace("-","")

        todo_task_id  = uuid.uuid1()
        todo_task_id = str(todo_task_id).replace("-","")

        now = datetime.now()
        str_now = now.strftime('%Y-%m-%d %H:%M:%S')
        #   新增表（yx_contract_trace）数据
        cur.execute(
            # claim_amount,is_paymenfile ,yx_contract_json,yx_attachment_json,yx_payment_apply_json,yx_payment_apply_log_json,payment_id,
                "insert into yx_finance_payment_trace(ID,BZ_ID,CREATE_TIME,CREATOR_DEPARTMENT_ID,CREATOR_DEPARTMENT_NAME,CREATOR_ID,CREATOR_NAME,IS_DELETED,UPDATE_TIME,UPDATOR_DEPARTMENT_ID,UPDATOR_DEPARTMENT_NAME,UPDATOR_ID,UPDATOR_NAME,VERSION,achieve_receive_bank,achieve_receive_company,balance_amount,isBehalfPayment,claim_note,claim_time,claimer_id,draft_expireDate,final_sure_date,hedge_name,hedge_note,hedge_time,infact_amount,mode,mode_name,note,number,pay_company,payment_business,receive_bank,receive_bank_account,receive_company,receive_company_name,status,sys_date,sys_status,time,transaction_number,transfer_bank,transfer_company,transfer_company_name,claimer_name)"
                
                " values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s"
                " )", (
                trace_id,k.bz_id,
                k.create_time.strftime("%Y-%m-%d %H:%M:%S") if not (k.create_time is None)   else str_now,
                k.creator_department_id,k.creator_department_name,
                k.creator_id,
                k.creator_name,
                k.is_deleted,
                k.update_time.strftime("%Y-%m-%d %H:%M:%S") if not (k.update_time is None)   else str_now,
                k.updator_department_id,
                k.updator_department_name,
                k.updator_id,
                k.updator_name,k.version,
                k.achieve_receive_bank, k.achieve_receive_company,
                k.balance_amount, k.isbehalfpayment, k.claim_note,
                k.claim_time .strftime("%Y-%m-%d %H:%M:%S") if not (k.claim_time is None)   else str_now,
                k.claimer_id,
                k.draft_expiredate.strftime("%Y-%m-%d %H:%M:%S") if not (k.draft_expiredate is None)   else str_now,
                k.final_sure_date.strftime("%Y-%m-%d %H:%M:%S") if not (k.final_sure_date is None)   else str_now,
                k.hedge_name, k.hedge_note,
                k.hedge_time .strftime("%Y-%m-%d %H:%M:%S") if not (k.hedge_time is None)   else str_now,
                k.infact_amount,
                k.mode, k.mode_name, k.nate, k.number, k.pay_company, k.payment_business,
                k.receive_bank, k.receive_bank_account, k.receive_company, k.receive_company_name, k.status,
                k.sys_date .strftime("%Y-%m-%d %H:%M:%S") if not (k.sys_date is None)   else str_now,
                k.sys_status,
                k.time .strftime("%Y-%m-%d %H:%M:%S") if not (k.time is None)   else str_now,
                k.transaction_number, k.transfer_bank, k.transfer_company,
                k.transfer_company_name, k.claimer_name))

        # 获取审批状态
        cur.execute("select t.approval_status from yx_workflow t where t.busi_id = %s ",(k.id))
        # cur.execute("select t.approval_status from yx_workflow t where t.busi_id = %s ",('8a8085c25d798682015d8702e471076b'))
        result_status = cur.fetchone()
        if result_status is None:
            result_status = '02'

        #   新增表（yx_finance_payment_apply）数据
        cur.execute("insert into yx_finance_payment_apply( ID,BZ_ID,CREATE_TIME,CREATOR_DEPARTMENT_ID,CREATOR_DEPARTMENT_NAME,CREATOR_ID,CREATOR_NAME,IS_DELETED,UPDATE_TIME,UPDATOR_DEPARTMENT_ID,UPDATOR_DEPARTMENT_NAME,UPDATOR_ID,UPDATOR_NAME,VERSION,apply_id,apply_incident_code,apply_incident_name,apply_user_account,apply_user_department_id,apply_user_name,current_assignee_account,current_assignee_name,trace_id,apply_status,apply_end_time,apply_start_time )"
                    " values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
                    (apply_id,k.bz_id,
                     k.create_time.strftime("%Y-%m-%d %H:%M:%S") if not (k.create_time is None)   else '',
                     k.creator_department_id,k.creator_department_name,k.creator_id,k.creator_name,k.is_deleted,
                     k.update_time.strftime("%Y-%m-%d %H:%M:%S") if not (k.update_time is None)   else str_now,
                     k.updator_department_id,k.updator_department_name,k.updator_id,k.updator_name,k.version,k.id,
                     '','' ,k.creator_id ,k.creator_department_id,k.creator_name, '', '',
                     trace_id,result_status, str_now, str_now))

        #   新增表（yx_act_ext_todo_task ）数据

        cur.execute(
            "insert into yx_act_ext_todo_task( id,bz_id,create_time,creator_department_id,creator_department_name,creator_id,creator_name,is_deleted,update_time,updator_department_id,updator_department_name,updator_id,updator_name,version,business_id,task_status) "
            # ",apply_end_time,apply_start_time,process_definition_key,settle_flag) "
            " values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
            (todo_task_id,k.bz_id,k.create_time,k.creator_department_id,k.creator_department_name,k.creator_id,k.creator_name,k.is_deleted,k.update_time,k.updator_department_id,k.updator_department_name,k.updator_id,k.updator_name,k.version,apply_id,result_status))

        print( apply_id, k.bz_id,k.create_time,k.creator_department_id,k.creator_department_name,k.creator_id,k.creator_name,k.is_deleted,k.update_time,k.updator_department_id,k.updator_department_name,k.updator_id,k.updator_name,k.version,k.id ,'','','' )

        # 写进审批日志
        re_count3 = cur.execute("select * from yx_workflow t where t.busi_id = %s ", (k.id))
        if re_count3 > 0:
            for l in generate_namedtuple(cur):
                log_id = uuid.uuid1()
                log_id = str(log_id).replace("-", "")
                print l
                # 更新表审批日志字段
                cur.execute(
                    "update yx_act_approve_record set business_id='" + apply_id + "' where process_instance_id = '" + l.process_instance_id + "' ")
cur.close()
conn.commit()
conn.close()
# for r in result:
        # print r
        # 写入trace 表
        # print (r['bill_number'],r['bill_type'],r['checker'])
        # print (r['bill_number'])
        # 写入 apply表
